/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sistemperpustakaan.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import javax.management.Query;
import javax.swing.JOptionPane;
import javax.swing.plaf.RootPaneUI;
import sistemperpustakaan.entity.User;
import sistemperpustakaan.entity.UserType;
import sistemperpustakaan.entity.ViewUser;
import sistemperpustakaan.utility.DbUtility;
import sistemperpustakaan.utility.UserUtility;
import sistemperpustakaan.view.user.DialogDetailUser;

/**
 *
 * @author EKo
 */
public class UserModel {

    Connection connection;
    ViewUser viewUser;
    Vector vectorType;
    List<ViewUser> listUser;
    List<UserType> listUserType;
    UserType userType;
    User user;
    DialogDetailUser dialogdetailuser;

    public UserModel() {
        this.connection = new DbUtility().getConnection();
    }

    public User getViewUser(String id) {
        String query = "SELECT * FROM users WHERE username= '" + id + "'";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (Exception ex) {
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return user;
    }

    public List<ViewUser> getListUser(String userId, String Type, String nama) {
        listUser = new ArrayList<ViewUser>();
        String query = "CALL pCariUser ('" + userId + "','" + Type + "','" + nama + "')";
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                viewUser = new ViewUser(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                listUser.add(viewUser);
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return listUser;
    }

    public Vector getVectorType() {
        vectorType = new Vector();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT nama FROM tipe_user");
            vectorType.add("");
            while (rs.next()) {
                vectorType.add(rs.getString(1));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return vectorType;
    }

    public List<UserType> getListUserType(int id, String nama) {
        listUserType = new ArrayList<UserType>();
        String query = "CALL pCariTipeUser (" + id + ",'" + nama + "')";
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(query);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                userType = new UserType(rs.getInt(1), rs.getString(2), rs.getString(3));
                listUserType.add(userType);

            }


            //JOptionPane.showMessageDialog(RootPane, rs.getRow());
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, "Error when insert data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return listUserType;
    }

    public boolean deleteUserType(int idType) {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("DELETE FROM tipe_user WHERE id=" + idType);
//            statement.setInt(1, idType);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when delete data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (SQLException ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public UserType getUserType(int idType) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("SELECT * FROM tipe_user WHERE id=" + idType + "");
//            statement.setInt(1, idType);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                userType = new UserType(rs.getInt(1), rs.getString(2), rs.getString(3));

            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return userType;
    }

    public boolean insertTypeUser(int id, String nama, String priv) {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("INSERT INTO tipe_user (id,nama, privilege) VALUES"
                    + "(fGenerateIdTipeUser(),'" + nama + "','" + priv + "')");
//            statement.setString(1, nama);
//            statement.setString(2, priv);
//            statement.setString(3, priv);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when insert  data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean updateTypeUser(int id, String nama, String priv, int id_lama) {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("UPDATE tipe_user SET id=" + id + ",nama='" + nama + "', "
                    + "privilege='" + priv + "' WHERE id='" + id_lama + "'");
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when update  data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public UserType generateIdUserType() {
        PreparedStatement statement = null;
        UserType usertype = null;
        try {
            statement = connection.prepareStatement("SELECT fGenerateIdTipeUser() AS Kode");
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                usertype = new UserType(rs.getInt(1), null, null);
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve data" + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return usertype;
    }

    public User getUser(String userId) {
        PreparedStatement statement = null;
        try {
            String query = "SELECT id,username, tipe_user_id, AES_DECRYPT(password,'ekosalemba'), nama FROM users WHERE username= '" + userId + "'";
            statement = connection.prepareStatement(query);
            //statement.setString(1, userId);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getString(5));
            }
            System.out.println();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve  data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return user;
    }

    public boolean insertUser(String userid, int type, String password, String nama) {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            String query = "INSERT INTO users (username, tipe_user_id, password, nama) VALUES ('" + userid + "'," + type + ", AES_ENCRYPT('" + password + "','ekosalemba'),'" + nama + "')";
            statement = connection.prepareStatement(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when insert  data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean updateUser(String userid, int type, String password, String nama_user, int id) {

        PreparedStatement statement = null;
        String query = "UPDATE Users SET username='" + userid + "',tipe_user_id=" + type + ",password=AES_ENCRYPT('" + password + "','ekosalemba'), tipe_user_id=" + type + ", nama='" + nama_user + "' WHERE id='" + id + "'";
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            //System.out.println(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when update  data \n Error: " + e.getMessage());

            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean updateUser2(String userid, int type, String password, String nama_user, String id_lama) {

        PreparedStatement statement = null;
        String query = "UPDATE Users SET user_id='" + userid + "',type=" + type + ", nama='" + nama_user + "' WHERE user_id='" + id_lama + "'";
        String query2 = "UPDATE Users SET user_id='" + userid + "',type=" + type + ",password=md5('" + password + "'), nama='" + nama_user + "' WHERE user_id='" + id_lama + "'";

        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query2);
            //System.out.println(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when update  data \n Error: " + e.getMessage());

            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean deleteUser(String id) {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement("DELETE FROM users WHERE user_id= '" + id + "'");
            //statement.setString(1, id);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when delete data \n Error: " + e.getMessage());
            try {
                connection.rollback();
            } catch (SQLException ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }

    public boolean isRegistered(String userName, String password) {
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement("SELECT * from users WHERE username= '" + userName + "' AND password= AES_ENCRYPT('" + password + "','ekosalemba')");

            ResultSet rs = statement.executeQuery();

            if (rs.next()) {
//                user = new User(rs.getString(1), rs.getInt(2), rs.getString(3), rs.getString(4));
//                UserUtility uu = new UserUtility(getUserType(rs.getInt(2)).getPrivilege());
//                System.out.println(rs.getInt(2));
                UserUtility.idUser = rs.getString(1);

//                UserUtility uu = new UserUtility("100000000000");
                UserUtility.name = rs.getString(5);


                return true;

            }
            UserUtility uu = new UserUtility(getUserType(rs.getInt(2)).getPrivilege());
            System.out.println(rs.getInt(2));
        } catch (Exception e) {
        } finally {

            try {
                if (statement != null) {

                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return false;
    }

    public User getUser(String userName, String password) {
        PreparedStatement statement = null;
        try {
            String query = "SELECT * from users WHERE username= '" + userName + "' AND password= aes_encrypt('" + password + "','ekosalemba')";
            statement = connection.prepareStatement(query);
            //statement.setString(1, userId);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                user = new User(rs.getInt(1), rs.getString(2), rs.getInt(4), rs.getString(3), rs.getString(5));
//                System.out.println(rs.getInt(2));
//                UserUtility uu = new UserUtility(getUserType(rs.getInt(2)).getPrivilege());
            }
            System.out.println();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve  data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return user;
    }

    public String getPassword(String userName) {
        PreparedStatement statement = null;
        String password = null;
        try {
            String query = "SELECT AES_DECRYPT(password,'ekosalemba')'password' from users WHERE id= '" + userName + "'";
            statement = connection.prepareStatement(query);
            //statement.setString(1, userId);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                password = rs.getString(1);
//                System.out.println(rs.getInt(2));
//                UserUtility uu = new UserUtility(getUserType(rs.getInt(2)).getPrivilege());
            }
//            System.out.println(password);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve  data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return password;
    }
//gak kepake

    public String getEncrypt(String password) {
        PreparedStatement statement = null;
        String passwordMd5 = null;
        try {
            String query = "SELECT md5('" + password + "');";
            statement = connection.prepareStatement(query);
            //statement.setString(1, userId);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                passwordMd5 = rs.getString(1);
//                System.out.println(rs.getInt(2));
//                UserUtility uu = new UserUtility(getUserType(rs.getInt(2)).getPrivilege());
            }
//            System.out.println(password);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when retrieve  data \n Error: " + e.getMessage());
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return passwordMd5;
    }

    public boolean ubahPassword(String userid, String passwordBaru) {

        PreparedStatement statement = null;
        String query = "UPDATE Users SET password=AES_ENCRYPT('" + passwordBaru + "','ekosalemba') WHERE id='" + userid + "'";

        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(query);
            //System.out.println(query);
            if (statement.executeUpdate() == 1) {
                connection.commit();
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error when update  data \n Error: " + e.getMessage());

            try {
                connection.rollback();
            } catch (Exception ex) {
            }
            return false;
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (Exception e) {
            }
        }
        return true;
    }
}
